/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.CES.DAOImplementation;

import com.bionic.CES.DAO.IQueryDAO;
import com.bionic.CES.beans.Currency;
import com.bionic.CES.beans.User;
import com.bionic.CES.beans.Query;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLQueryDAO extends MYSQLDAO implements IQueryDAO {

    @Override
    public void add(Query query) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO query "
                    + "(`OpenByUser`, `OpenDate`, `FinishDate`, `Amount`, "
                    + "`CurrencyFrom`, `CurrencyTo`, `RateFrom`, `RateTo`, `Status`)  "
                    + "VALUES(?,?,?,?,?,?,?,?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);

            int id = query.getUserId();
            User user = new User();
            user.setUserid(id);
            boolean b = new MySQLUserDAO().findByDynamicSelect(user).isEmpty();
            if (!b) {
                ptmt.setInt(1, query.getUserId());
            } else {
                throw new SQLException("Such User with id=" + query.getUserId() + " doesnt exists");
            }
            ptmt.setDate(2, query.getOpenDate());
            ptmt.setDate(3, query.getFinishDate());
            ptmt.setDouble(4, query.getAmount());
            String shortname = query.getCurrencyFrom();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(5, query.getCurrencyFrom());
            } else {
                throw new SQLException("Such currency with shortname=" + query.getCurrencyFrom() + " doesnt exists");
            }
            shortname = query.getCurrencyTo();
            currency = new Currency();
            currency.setShortName(shortname);
            b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(6, query.getCurrencyTo());
            } else {
                throw new SQLException("Such currency with shortname=" + query.getCurrencyTo() + " doesnt exists");
            }
            ptmt.setDouble(7, query.getRateFrom());
            ptmt.setDouble(8, query.getRateTo());
            ptmt.setString(9, query.getStatus());

            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data Added Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(Query query) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE query SET `OpenByUser`=?, `OpenDate`=?, `FinishDate`=?, `Amount`=?, "
                    + "`CurrencyFrom`=?,`CurrencyTo`=?,`RateFrom`=?,`RateTo`=?,`Status`=?"
                    + "WHERE `id`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);

            int id = query.getUserId();
            User user = new User();
            user.setUserid(id);
            boolean b = new MySQLUserDAO().findByDynamicSelect(user).isEmpty();
            if (!b) {
                ptmt.setInt(1, query.getUserId());
            } else {
                throw new SQLException("Such User with id=" + query.getUserId() + " doesnt exists");
            }
            ptmt.setDate(2, query.getOpenDate());
            ptmt.setDate(3, query.getFinishDate());
            ptmt.setDouble(4, query.getAmount());
            String shortname = query.getCurrencyFrom();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(5, query.getCurrencyFrom());
            } else {
                throw new SQLException("Such currency with shortname=" + query.getCurrencyFrom() + " doesnt exists");
            }
            shortname = query.getCurrencyTo();
            currency = new Currency();
            currency.setShortName(shortname);
            b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(6, query.getCurrencyTo());
            } else {
                throw new SQLException("Such currency with shortname=" + query.getCurrencyTo() + " doesnt exists");
            }
            ptmt.setDouble(7, query.getRateFrom());
            ptmt.setDouble(8, query.getRateTo());
            ptmt.setString(9, query.getStatus());

            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data Updated Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteById(int id) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM query WHERE `id`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, id);
            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data deleted Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Query> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<Query> result = new ArrayList();
            String queryString = "SELECT * FROM query";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Query query = new Query();
                query.setQueryId(resultSet.getInt("id"));
                query.setUserId(resultSet.getInt("OpenByUser"));
                query.setOpenDate(resultSet.getDate("OpenDate"));
                query.setFinishDate(resultSet.getDate("FinishDate"));
                query.setAmount(resultSet.getDouble("Amount"));
                query.setCurrencyFrom(resultSet.getString("CurrencyFrom"));
                query.setCurrencyTo(resultSet.getString("CurrencyTo"));
                query.setRateFrom(resultSet.getDouble("RateFrom"));
                query.setRateTo(resultSet.getDouble("RateTo"));
                query.setStatus(resultSet.getString("Status"));
                result.add(query);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Query> findByDynamicSelect(Query query) throws SQLException {
        connection = getConnection();
        try {
            List<Query> result = new ArrayList();
            String queryString = "SELECT * FROM query WHERE ";
            int i = 0;
            if (query.getQueryId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`id`=" + query.getQueryId();
                i++;
            }
            if (query.getUserId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`OpenByUser`=" + query.getUserId();
                i++;
            }
            if (query.getOpenDate() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`OpenDate`=\"" + query.getOpenDate() + "\"";
                i++;
            }
            if (query.getFinishDate() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`FinishDate`=\"" + query.getFinishDate() + "\"";
                i++;
            }
            if (query.getAmount() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Amount`=" + query.getAmount();
                i++;
            }
            if (query.getCurrencyFrom() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`CurrencyFrom`=\"" + query.getCurrencyFrom() + "\"";
                i++;
            }
            if (query.getCurrencyTo() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`CurrencyTo`=\"" + query.getCurrencyTo() + "\"";
                i++;
            }
            if (query.getRateFrom() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`RateFrom`=" + query.getRateFrom();
                i++;
            }
            if (query.getRateTo() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`RateTo`=" + query.getRateTo();
                i++;
            }
            if (query.getStatus() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Status`=\"" + query.getStatus() + "\"";
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                query = new Query();
                query.setQueryId(resultSet.getInt("id"));
                query.setUserId(resultSet.getInt("OpenByUser"));
                query.setOpenDate(resultSet.getDate("OpenDate"));
                query.setFinishDate(resultSet.getDate("FinishDate"));
                query.setAmount(resultSet.getDouble("Amount"));
                query.setCurrencyFrom(resultSet.getString("CurrencyFrom"));
                query.setCurrencyTo(resultSet.getString("CurrencyTo"));
                query.setRateFrom(resultSet.getDouble("RateFrom"));
                query.setRateTo(resultSet.getDouble("RateTo"));
                query.setStatus(resultSet.getString("Status"));
                result.add(query);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }
}
